package com.quintal.androidtouchcloud.storage;

import java.io.File;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.os.Environment;
import android.util.Log;


/**
 * Created by filipequintal on 6/1/13.
 */
public final class DBManager {

    private final static String MODULE = "DBManager";
    private final static String DB_NAME = "touchCloud.db";

    private final static String ANCHOR_EVENTS_TABLE = "anchor_events";
    private final static String READ_EVENTS_TABLE = "read_events";
    private final static String USERS_TABLE = "users";
    private final static String LOCAL_FILES_TABLE = "local_files";

    private static SQLiteDatabase db;

    private DBManager(){
        try{
            if(db==null){
                db = SQLiteDatabase.openDatabase(Environment.getExternalStorageDirectory()+"/touchCloud/"+DB_NAME,null,0);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    private static class DBManagerHolder{

        private static final DBManager INSTANCE = new DBManager();

    }

    public static DBManager getDBManager(){
        return DBManagerHolder.INSTANCE;
    }

    public void closeDB(){
      try{
        if(db!=null){
            db.close();
        }
      }catch(Exception e){
          e.printStackTrace();
      }
    }
    public static boolean databaseExists()	{

        File storage_file = new File(Environment.getExternalStorageDirectory(), "/touchCloud/"+DB_NAME);

        return storage_file.exists();
    }
    public static void initDatabase(){
        try{
            File db_storage_file = new File(Environment.getExternalStorageDirectory(), "/touchCloud");
            if(!db_storage_file.exists())
                if(! db_storage_file.mkdir()){
                    Log.e(MODULE,"error creating /touchCloud directory");
                    return;
                }
            db_storage_file = new File(db_storage_file, DB_NAME);

            if(! db_storage_file.exists()){
                db_storage_file.createNewFile();
                createDatabase(db_storage_file.getAbsolutePath());
            }
        }catch(Exception e){

        }
    }
    private static void createDatabase(String path){
        Log.i(MODULE, "Creating new database");
        db = SQLiteDatabase.openDatabase(
                path,
                null,
                SQLiteDatabase.CREATE_IF_NECESSARY);
        // create anchor events table
        String sql = "CREATE TABLE "+ANCHOR_EVENTS_TABLE+" (" +
                "nfc_id  text NOT NULL, " +
                "user_id integer NOT NULL, " +
                "event_time  real NOT NULL, " +
                "PRIMARY KEY(event_time)" +
                ")";
        db.execSQL(sql);

        sql =  "CREATE TABLE "+READ_EVENTS_TABLE+" (" +
                "nfc_id text NOT NULL, " +
                "user_id integer, " +
                "device text, " +
                "event_time real NOT NULL," +
                "PRIMARY KEY(event_time)" +
                ")";
        db.execSQL(sql);
        
        sql =   "CREATE TABLE "+LOCAL_FILES_TABLE+" ("+
				"id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "+
				"revision  TEXT NOT NULL, "+
				"name  TEXT NOT NULL, "+
				"dropbox_path  TEXT NOT NULL, "+
				"original_share_link TEXT NOT NULL, "+
				"hash_code TEXT NOT NULL"+
				")";
        db.execSQL(sql);
				
    }
    public synchronized boolean insertAnchorEvents(String nfc_id, int user_id, long tms){
    	SQLiteStatement stm = null;
    	
    	String sql = "INSERT INTO "+ANCHOR_EVENTS_TABLE+"(nfc_id,user_id,event_time)" +
    					" VALUES('"+nfc_id+"',"+user_id+", "+tms+")";
    	
    	stm = db.compileStatement(sql);
    	
    	if(stm.executeInsert()>0)
    		return true;
    	else
    		return false;
    }
    public synchronized boolean insertReadEvents(String nfc_id, int user_id, String device,long tms){
    	SQLiteStatement stm = null;
    	
    	String sql = "INSERT INTO "+READ_EVENTS_TABLE+"(nfc_id,user_id,device,event_time)" +
    					" VALUES('"+nfc_id+"',"+user_id+",'"+device+"',"+tms+")";
    	
    	stm = db.compileStatement(sql);
    	
    	if(stm.executeInsert()>0)
    		return true;
    	else
    		return false;
    }
    
    public synchronized int[] countTags(int user_id){
    	int[] result = new int[2];
    	SQLiteStatement stm = null;
    	
    	String sql = "SELECT COUNT(user_id)  from anchor_events WHERE user_id="+user_id;
    	String sql2 = "SELECT COUNT(user_id)  from read_events WHERE user_id="+user_id;
    	
    	stm = db.compileStatement(sql);
    	result[0] = (int)stm.simpleQueryForLong();
    	
    	stm = db.compileStatement(sql2);
    	result[1] = (int)stm.simpleQueryForLong();
    	
    	return result;
    }
    public synchronized boolean insertLocalFileInfo(String name, String path, String revision, String original_link, String file_hash ){
    	Log.i(MODULE, "file info inserted locally");
    	SQLiteStatement stm = null;
    	String sql = "INSERT INTO "+LOCAL_FILES_TABLE+"(revision,name,dropbox_path,original_share_link,hash_code) "+
    					"VALUES('"+revision+"','"+name+"','"+path+"','"+original_link+"','"+file_hash+"')";
    	stm = db.compileStatement(sql);
    	if(stm.executeInsert()>0){
    		Log.i(MODULE, "new local file inserted");
    		return true;
    	}else
    		return false;
    	
    }
    public synchronized boolean updateLocalFileInfo(String path, String revision ){
    	SQLiteStatement stm = null;
    	String sql = "UPDATE "+LOCAL_FILES_TABLE+" SET revision='"+revision+"' "+
    					"WHERE dropbox_path='"+path+"'";
    	stm = db.compileStatement(sql);
    	int result = stm.executeUpdateDelete();
    	Log.i(MODULE, "new local file updated DEB"+result);
    	return result>0;
    	
    }
    public String[] getFileMetaData(String file_name){
    	String[] result = new String[3];
    	result[0]=file_name;
    	String sql = "SELECT revision,dropbox_path FROM "+LOCAL_FILES_TABLE+" "+
    					"WHERE name='"+file_name+"'";
    	Cursor c = db.rawQuery(sql, null);
    	while(c.moveToNext()){
    		result[1] = c.getString(0);
    		result[2] = c.getString(1);
		}
    	return result;
    }
    /**
     * 
     * @param share_link - original share link
     * @return Array with metadata [dp_path,revision,name]
     */
    public String[] getMetadataFromShareLink(String share_link){
    	Log.i(MODULE, "Looking for share link");
    	String[] result = new String[3];
    	result[0]="";
    	result[1]="";
    	result[2]="";
    	String sql = "SELECT dropbox_path,revision,name FROM "+LOCAL_FILES_TABLE+" "+
    					"WHERE original_share_link='"+share_link+"'";
    	Cursor c = db.rawQuery(sql, null);
    	while(c.moveToNext()){
    		result[0] = c.getString(0);
    		result[1] = c.getString(1);
    		result[2] = c.getString(2);
		}
    	return result;
    }
   /**
    * 
    * @param hash_code - original hash code
    * @return Array with the metadata [dp_path,revision,name] 
    */
    public String[] getMetadataFromHash(String hash_code){
    	Log.i(MODULE, "Looking for hash code");
    	String[] result = new String[3];
    	result[0]="";
    	result[1]="";
    	result[2]="";
    	String sql = "SELECT original_share_link,revision,name FROM "+LOCAL_FILES_TABLE+" "+
    					"WHERE hash_code='"+hash_code+"'";
    	Cursor c = db.rawQuery(sql, null);
    	while(c.moveToNext()){
    		result[0] = c.getString(0);
    		result[1] = c.getString(1);
    		result[2] = c.getString(2);
		}
    	return result;
    }
}